package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;


import ModelLayer.Customer;

public class DBCustomer {
	private static Connection con;
	Statement stmt;
	public DBCustomer() 
	{
		con = DbConnection.getInstance().getDBcon();
	}
	public void createCustomer(String fname,String lname,String address,String city,int zip,String phonenr,int ssn)
	{	String query="";
		try{
			int cID=GetMax.getMaxId("Select max(cID) from Customer")+1;//Gets the maximum cID from the DB and adds+1 for the next ID
			
			 query ="INSERT INTO Customer( cID, fname, lname, address, city, zip, phonenr, ssn)  VALUES('"+cID+"','"+fname+"','"+lname+"','"+address+"','"+city+"','"+zip+"','"+phonenr+"','"+ssn+"')";
			
			 Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{	//System.out.println("SuID,clID or gID is most probably invalid!");
			e.printStackTrace();
			}
	}
	public void deleteCustomer(int cID)
	{	//cID=1; 
		try{
			String query="DELETE FROM Customer WHERE cID = '"+cID+"'";
			Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{e.printStackTrace();
		}
	}
	public void updateCustomer(Customer g)
	{ Customer suObj = g;
	String query="UPfname Customer SET "+
		 	  "fname ='"+ suObj.getFname()+"', "+
		 	  "lname ='"+ suObj.getFname() + "'" +
		 	 "address ='"+ suObj.getAddress() + "'" +
		 	"city ='"+ suObj.getCity() + "'" +
		 	"zip ='"+ suObj.getZip() + "'" +
		 	"phonenr ='"+ suObj.getPhone() + "'" +
		 	"ssn ='"+ suObj.getSsn() + "'" +
		 	      " WHERE cID = '"+ suObj.getcID() + "'";
              System.out.println("Upfname query:" + query);
              try{
            	  Statement stmt = con.createStatement();
            	  stmt.setQueryTimeout(5);
            	  stmt.executeUpdate(query);
            	  stmt.close();
            	  
              }
              catch(Exception e)
              {e.printStackTrace();}
		
	}
	
	private String buildQuery()
	{
	    String query="SELECT cID, fname, lname, address, city, zip, phonenr, ssn  FROM Customer";
		/*
		if (wClause.length()>0)
			query=query+" WHERE ";
			*/
		return query;
	}
	public ArrayList<Customer> miscWhere()
	{
            ResultSet results;
	    ArrayList<Customer> list = new ArrayList<Customer>();	
		
	    String query =  buildQuery();
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Customer empObj = new Customer();
		 empObj = buildCustomer(results);	
                 list.add(empObj);	
		}//end while
                 stmt.close();     
                 
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	public Customer findCustomer(int cID)
	{
		ResultSet results;
		Customer empObj = new Customer();
                
	        String query =  buildQuery() + " WHERE cID ="+"'"+cID+"'";
                System.out.println(query);
		try{ // 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            empObj = buildCustomer(results);
                            //assocaition is to be build
                            stmt.close();
                            
                            
			}
                        else{ //no Customer was found
                            empObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return empObj;
	}
	private Customer buildCustomer(ResultSet results)
    {   Customer suObj = new Customer();
        try{ // the columns from the table glayee  are used
        	//cID, fname, lname, address, delivery_fname, zip, cID, ssn
              suObj.setcID(results.getInt("cID"));
              suObj.setFname(results.getString("fname"));
              suObj.setLname(results.getString("lname"));
              suObj.setAddress(results.getString("address"));
              suObj.setCity(results.getString("city"));
              suObj.setZip(results.getInt("zip"));
              suObj.setPhone(results.getString("phonenr"));
              suObj.setSsn(results.getInt("ssn"));
              
              
        }
       catch(Exception e)
       {
           System.out.println("Error in building the Customer object");
       }
       return suObj;
}
	
}